package com.tom.springboot3demo;

import com.tom.springboot3demo.constent.UrlContent;
import com.tom.springboot3demo.utils.ExcelUtils;
import com.tom.springboot3demo.vo.ExcelMergeVo2;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

/**
 * excel合并单元格
 */
public class ExcelMerger {


    public static void main(String[] args) throws IOException {
        //文件路径
        String filePath = UrlContent.test1_url;
        String filePath2 = UrlContent.test2_url;

        //文件输入流
        InputStream in = new FileInputStream(filePath);
        InputStream in2 = new FileInputStream(filePath2);

        //接收合并区域范围
        ArrayList<ExcelMergeVo2> arrColumn4 = new ArrayList<>();

        XSSFWorkbook wb = new XSSFWorkbook(in);
        XSSFSheet sheet = wb.getSheetAt(0);
        //XSSFSheet sheet = wb.getSheet("20241206");


        XSSFWorkbook wb2 = new XSSFWorkbook(in2);
        XSSFSheet sheet2 = wb2.getSheetAt(0);

        //获取合并区域
        int sheetmergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetmergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            //获取合并区域值
            String cellValue = ExcelUtils.getNewCellValue(sheet.getRow(firstRow).getCell(firstColumn));
            System.out.println("获取合并区域值【原始格式：】"+cellValue);
            //合并区域添加到集合中
            arrColumn4.add(new ExcelMergeVo2(cellValue, firstRow, lastRow, firstColumn, lastColumn));

        }

        int lastRowNum = sheet.getLastRowNum();
        System.out.println("lastRowNum:"+  sheet.getLastRowNum());
        //int physicalNumberOfCells = sheet.getRow(0).getPhysicalNumberOfCells();
        //将合并区域写入到另一个excel中
        for (int i = 0; i <= lastRowNum; i++) {
            int physicalNumberOfCells = sheet.getRow(i).getPhysicalNumberOfCells();
            XSSFRow row = sheet.getRow(i);
            XSSFRow row2 = sheet2.createRow(i);
            for (int j = 0; j <= physicalNumberOfCells; j++) {
                XSSFCell cell = row.getCell(j);
                String cellValue = ExcelUtils.getNewCellValue(cell);

                //合并单元格拆分后填充内容
                if(cellValue == null||"".equals(cellValue)){
                    cellValue = ExcelUtils.getExcelValue(arrColumn4, i, j);
                }

                row2.createCell(j).setCellValue(cellValue);

            }

        }
        //文件输出流
        FileOutputStream out = new FileOutputStream(filePath2);


        wb2.write(out);
        wb.close();
        in.close();
        out.close();

    }


}